USE [ConversionDB]
GO
/****** Object:  StoredProcedure [dbo].[SyncScheduleDataFromPS]    Script Date: 03/30/2016 17:26:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Taylor Chase
-- Create date: 10 September 2015
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[SyncScheduleDataFromPS] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	PRINT 'Delete current schedule data in Student Database'
	-- Delete the current student schedules from the student database
	DELETE OPENQUERY(STUDDATA97,'SELECT * FROM [Student Courses]')
	WHERE [YEAR] IN (SELECT OLDRVAYEAR FROM MBOA.CoreData.dbo.Current_YearTerm)

	-- Delete the current course schedule from the student database
	DELETE OPENQUERY(STUDDATA97,'SELECT * FROM [Master Schedule]')
	WHERE [YEAR] IN (SELECT OLDRVAYEAR FROM MBOA.CoreData.dbo.Current_YearTerm)

	PRINT 'Insert current schedule data from PowerSchool to Student Database'
	PRINT 'Insert Master Schedule'
	-- Insert the current course schedule from PowerSchool to the student database
	-- The group by query prevents courses that meet in different rooms with different teachers
	-- from causing the student database problems.
	INSERT INTO OPENQUERY(STUDDATA97,'SELECT [Year],[Term],[Period],[CourseID],[Section],[SchoolID],[StaffID],[Room] FROM [Master Schedule]')
	SELECT [RVAYEAR],[RVATERM],[RVAPERIOD],[OldRVAID],[Sect],[SCHOOLID],MAX([TeacherID]),MAX([ROOM])
	  FROM MBOA.[CoreData].[dbo].[PS_MASTERSCHEDULE_TOACCESS]
	  GROUP BY [RVAYEAR],[RVATERM],[RVAPERIOD],[OldRVAID],[Sect],[SCHOOLID]

	PRINT 'Insert Student Courses'	  
	-- Insert the current student schedules from PowerSchool to the student database
	-- The group by query prevents students in multiple copies of the same course in different periods 
	-- from causing the student database problems. (For example, multiple study halls.)
	INSERT INTO OPENQUERY(STUDDATA97,'SELECT * FROM [Student Courses]')
	SELECT [STUDENTID],[YEAR],[RVATERM],[OldRVAID],MIN([SECTION]),[SCHOOLID],MIN([PER])
	  FROM MBOA.[CoreData].[dbo].[RS_STUDENT_SCHEDULE_TOACCESS]
	  GROUP BY [STUDENTID],[YEAR],[RVATERM],[OldRVAID],[SCHOOLID]

	PRINT 'Finished Schedule Sync'
	-- If there are more issues in the future, these two queries check for duplicate values in order 
	-- to assist in troubleshooting.
	--SELECT A.*,B.*
	--  FROM MBOA.[CoreData].[dbo].[PS_MASTERSCHEDULE_TOACCESS] A INNER JOIN  MBOA.[CoreData].[dbo].[PS_MASTERSCHEDULE_TOACCESS] B
	--  ON A.RVAYEAR = B.RVAYEAR AND A.RVATERM=B.RVATERM AND A.RVAPERIOD=B.RVAPERIOD AND A.OldrvaID = B.OLDRVAID AND A.SECT = B.SECT
	--  AND A.SchoolID = B.Schoolid 
	--  WHERE A.TeacherID <> B.TeacherID AND A.room <> B.room
	--SELECT A.*,B.*
	--  FROM MBOA.[CoreData].[dbo].[RS_STUDENT_SCHEDULE_TOACCESS] A INNER JOIN MBOA.[CoreData].[dbo].[RS_STUDENT_SCHEDULE_TOACCESS] B
	--	ON A.StudentID = B.StudentID AND A.Year=B.Year AND A.Rvaterm = b.rvaterm AND A.oldrvaid = B.oldrvaid and a.schoolid = b.schoolid
	--	WHERE A.Section <> B.Section OR A.Per <> B.Per  

END
